USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[PreRoundKiroku]    スクリプト日付: 03/17/2026 11:06:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PreRoundKiroku]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PreRoundKiroku]
GO

CREATE PROCEDURE [dbo].[PreRoundKiroku]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
	@NOW int

AS
SELECT 
	R.TAIKAINO,
	R.KYOGINO,
	R.SUBNO,
	R.ROUNDNO,
	RE.KUMINO,
	RE.LANE,
	L.KYOGISYANO,
	(CASE 
		WHEN RE.EXTEND2 = '1' THEN CEILING(RE.KIROKU *10) / 10 
		WHEN RE.EXTEND2 = '2' THEN CEILING(RE.KIROKU *100) / 100 
		ELSE RE.KIROKU
	END) as KIROKU
FROM TBL_ROUND R 
	LEFT JOIN TBL_RESULT RE 
		ON R.TAIKAINO = RE.TAIKAINO AND R.KYOGINO = RE.KYOGINO 
		AND R.SUBNO = RE.SUBNO AND R.ROUNDNO = RE.ROUNDNO
	LEFT JOIN TBL_LANE L 
		ON RE.TAIKAINO = L.TAIKAINO AND RE.KYOGINO = L.KYOGINO 
		AND RE.SUBNO = L.SUBNO AND RE.ROUNDNO = L.ROUNDNO
		AND RE.KUMINO = L.KUMINO AND RE.LANE = L.LANE
	
WHERE R.TAIKAINO = @TAIKAINO AND R.KYOGINO = @KYOGINO 
	AND R.SUBNO = @SUBNO AND R.ROUNDNO = (-- 直近のラウンドのみに限定
		select top 1 MAX(ROUNDNO) from TBL_ROUND 
		where TAIKAINO = @TAIKAINO AND KYOGINO = @KYOGINO 
		AND SUBNO = @SUBNO AND ROUNDNO < @NOW 
	)
